package com.libweb.model.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.tomcat.jni.Time;

import com.libweb.model.dto.Rent;
import com.libweb.model.dto.Book;

public class RentDao {
	
	public void insertRent(Rent rent) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			//1. 드라이버 로딩
			Class.forName("com.mysql.jdbc.Driver");
			//2. 연결 생성
			conn = DriverManager.getConnection("jdbc:mysql://172.16.3.13:3306/libraryweb",	//url
					"instructor", "mysql");													//account
			
			
			//3. 명령 생성
			String sql = "INSERT INTO rent "
					+ "(bookno, memberid) "
					+ "VALUES (?, ?);";	
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, rent.getBookNo());
			pstmt.setString(2, rent.getMemberId());
			
			//4. 명령 실행
			pstmt.executeUpdate();
		}catch (Exception ex) {
			
		}finally {
			try { pstmt.close(); } catch(Exception ex) {}
			try { conn.close(); } catch(Exception ex) {}
		}		
	}
	
	public List<Rent> getRents() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Book book = null;
		ArrayList<Rent> rents = new ArrayList<Rent>();
		
		try {
			//2. 연결 생성
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = "SELECT r.rentNo, r.memberid, r.bookno, b.title, b.author, b.publisher, r.rentdate, r.reversedate, r.status, r.reverseready " 
					+ "From rent r "
					+ "INNER JOIN book b "
					+ "ON r.bookno=b.bookno Order by r.rentno";
			pstmt = conn.prepareStatement(sql);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			while(rs.next()) {
				Rent rent = new Rent();
				book = new Book();
				
				rent.setRentNo(rs.getInt(1));
				rent.setMemberId(rs.getString(2));
				rent.setBookNo(rs.getInt(3));
				book.setTitle(rs.getString(4));
				book.setAuthor(rs.getString(5));
				book.setPublisher(rs.getString(6));
				rent.setRentDate(rs.getDate(7));
				rent.setReverseDate(rs.getDate(8));
				rent.setStatus(rs.getString(9));
				rent.setReverseReady(rs.getString(10));				
				rent.setBook(book);
				
				rents.add(rent);
			}
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally {
			try { rs.close(); } catch(Exception ex) {}
			try { pstmt.close(); } catch(Exception ex) {}
			try { conn.close(); } catch(Exception ex) {}
		}
		return rents;
	}
	
	public List<Rent> getRentByMemberId(String memberid) {
			
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Rent rent = null;
		Book book = null;
		ArrayList<Rent> rents = new ArrayList<Rent>();
	
		try {
			//2. 연결 생성
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = "SELECT r.rentNo, r.memberid, r.bookno, b.title, b.author, b.publisher, r.rentdate, r.reversedate, r.status, r.reverseready " 
						+ "From rent r "
						+ "INNER JOIN book b "
						+ "ON r.bookno=b.bookno "
						+ "WHERE memberid = ? Order by r.rentno";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,memberid);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			while (rs.next()) {
				rent = new Rent();
				book = new Book();
				
				rent.setRentNo(rs.getInt(1));
				rent.setMemberId(rs.getString(2));
				rent.setBookNo(rs.getInt(3));
				book.setTitle(rs.getString(4));
				book.setAuthor(rs.getString(5));
				book.setPublisher(rs.getString(6));
				rent.setRentDate(rs.getDate(7));
				rent.setReverseDate(rs.getDate(8));
				rent.setStatus(rs.getString(9));
				rent.setReverseReady(rs.getString(10));				
				rent.setBook(book);
				
				rents.add(rent);
			}
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally {
			try { rs.close(); } catch(Exception ex) {}
			try { pstmt.close(); } catch(Exception ex) {}
			try { conn.close(); } catch(Exception ex) {}
		}
		return rents;
	}
	
	public void rentAgree(int rentno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			
			Calendar c = Calendar.getInstance();

			int year = c.YEAR; 
			int month = c.MONTH;
			int day = c.DAY_OF_MONTH;
			
			Date rentDate = new Date(c.getTimeInMillis());
			
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE rent " +
				"SET status = ?, rentdate = ? " + 
				"WHERE rentno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "onrent");
			pstmt.setDate(2, rentDate);
			pstmt.setInt(3, rentno);
			
			
			pstmt.executeUpdate();
		} catch (Exception ex) {			
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}		
	}
	
	public void reverseRequest(int rentno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE rent " +
				"SET reverseready = ? " + 
				"WHERE rentno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "onreverse");
			pstmt.setInt(2, rentno);
			
			
			pstmt.executeUpdate();
		} catch (Exception ex) {			
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}		
	}
	
	public void reverseAgree(int rentno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {

			Calendar c = Calendar.getInstance();

			int year = c.YEAR; 
			int month = c.MONTH;
			int day = c.DAY_OF_MONTH;
			
			Date reverseDate = new Date(c.getTimeInMillis());
			
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE rent " +
				"SET reverseready = ?, status = ? , reversedate = ? "+ 
				"WHERE rentno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "reverse");
			pstmt.setString(2, "reverse");
			pstmt.setDate(3, reverseDate);
			pstmt.setInt(4, rentno);
			
			
			pstmt.executeUpdate();
		} catch (Exception ex) {			
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}		
	}
/*
	public void deleteBook(int bookNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
						
			String sql = "DELETE FROM book WHERE bookno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,  bookNo);
			
			pstmt.executeUpdate();
						
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}		
		
	}

	public void updateBook(Book book) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE book " +
				"SET title = ?, publisher = ?, author = ?, pubdate = ? " + 
				"WHERE bookno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, book.getTitle());
			pstmt.setString(2, book.getPublisher());
			pstmt.setString(3, book.getAuthor());
			pstmt.setDate(4, book.getPubDate());
			pstmt.setInt(5, book.getBookNo());
			
			pstmt.executeUpdate();
		} catch (Exception ex) {			
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}		
	}*/
}
